import logging
from datetime import date
from typing import Optional, List

from fastapi import APIRouter, Depends, Query
from pydantic import Field, BaseModel
from sqlalchemy import text
from sqlalchemy.exc import SQLAlchemyError
from sqlalchemy.orm import Session

from commons.BaseResponse import BaseResponse
from commons.PaginationBase import PaginationBase
from datebase import get_db
from utils.EncryptionUtil import oauth2_scheme, EncryptionMiddleware

mov_head_router = APIRouter(prefix="/issue/movHead", tags=["设备调拨管理"], dependencies=[Depends(oauth2_scheme)])


class MovHeadSearch(PaginationBase):
    fsiteNo: str = Field(None, description="")
    ftakedateStart: str = Field(None, description="")
    ftakedateEnd: str = Field(None, description="")
    fpointNoOut: str = Field(None, description="")
    foutpointNo2: str = Field(None, description="")
    fbillno: str = Field(None, description="")
    fmovuser: str = Field(None, description="")
    fbillstate: str = Field(None, description="")


@mov_head_router.get("/list", summary="查询设备调拨数据")
def list_mov_head(mov: MovHeadSearch = Query(), db: Session = Depends(get_db)):
    param = {}
    sql = "select count(1) from lims.s_tc_mov_head where 1=1"
    if mov.fsiteNo is not None:
        sql = sql + " and fsite_no = :site_no "
        param["site_no"] = mov.fsiteNo
    if mov.fpointNoOut is not None:
        sql = sql + " and fpoint_no_out = :fpointNoOut "
        param["fpointNoOut"] = mov.fpointNoOut
    if mov.foutpointNo2 is not None:
        sql = sql + " and foutpoint_no2 = :foutpointNo2 "
        param["foutpointNo2"] = mov.foutpointNo2
    if mov.fbillno is not None:
        sql = sql + " and fbillno like concat('%', :fbillno ,'%')"
        param["fbillno"] = mov.fbillno
    if mov.fmovuser is not None:
        sql = sql + " and fmovuser = :fmovuser "
        param["fmovuser"] = mov.fmovuser
    if mov.fbillstate is not None:
        sql = sql + " and fbillstate = :fbillstate "
        param["fbillstate"] = mov.fbillstate
    if (mov.ftakedateStart is not None) and (mov.ftakedateEnd is not None):
        sql = sql + " and to_char(fmovdate,'yyyymmdd') between :ftakedateStart and :ftakedateEnd"
        param["ftakedateStart"] = mov.ftakedateStart
        param["ftakedateEnd"] = mov.ftakedateEnd
    count = db.execute(text(sql), param).scalar()

    param = {}
    sql = "select fbillno,fsite_no,fmovuser,fmovdate,fpoint_no_out,foutpoint_no2,fitemcount,fpiececount,"
    sql = sql + " ftotalamt,fexplanation,fupddate,foperator,fcheckdate,fcheckuser,fbillstate"
    sql = sql + " from lims.s_tc_mov_head where 1=1"
    if mov.fsiteNo is not None:
        sql = sql + " and fsite_no = :site_no "
        param["site_no"] = mov.fsiteNo
    if mov.fpointNoOut is not None:
        sql = sql + " and fpoint_no_out = :fpointNoOut "
        param["fpointNoOut"] = mov.fpointNoOut
    if mov.foutpointNo2 is not None:
        sql = sql + " and foutpoint_no2 = :foutpointNo2 "
        param["foutpointNo2"] = mov.foutpointNo2
    if mov.fbillno is not None:
        sql = sql + " and fbillno like concat('%', :fbillno ,'%')"
        param["fbillno"] = mov.fbillno
    if mov.fmovuser is not None:
        sql = sql + " and fmovuser = :fmovuser "
        param["fmovuser"] = mov.fmovuser
    if mov.fbillstate is not None:
        sql = sql + " and fbillstate = :fbillstate "
        param["fbillstate"] = mov.fbillstate
    if (mov.ftakedateStart is not None) and (mov.ftakedateEnd is not None):
        sql = sql + " and to_char(fmovdate,'yyyymmdd') between :ftakedateStart and :ftakedateEnd"
        param["ftakedateStart"] = mov.ftakedateStart
        param["ftakedateEnd"] = mov.ftakedateEnd
    if mov.order == "desc":
        sql = sql + " order by  fmovdate,fbillno desc "
    else:
        sql = sql + " order by  fmovdate,fbillno "
    sql = sql + " limit :limit offset :offset "
    param["offset"] = mov.offset
    param["limit"] = mov.limit
    data = db.execute(text(sql), param).fetchall()

    # details 明细列表
    param = {}
    sql_3 = " select b.fbillno,b.fline,b.fpoint_size_out,b.fpoint_size_in,b.fequipmentbarcode,b.feuipmentid,"
    sql_3 = sql_3 + " b.fmacno,b.fequipmenttag,b.flinkmac,b.flinkmark,b.fbuyprice"
    sql_3 = sql_3 + " from lims.s_tc_mov_head a,lims.s_tc_mov_detail b where a.fbillno=b.fbillno "
    if mov.fsiteNo is not None:
        sql_3 = sql_3 + " and fsite_no = :site_no "
        param["site_no"] = mov.fsiteNo
    if mov.fpointNoOut is not None:
        sql_3 = sql_3 + " and fpoint_no_out = :fpointNoOut "
        param["fpointNoOut"] = mov.fpointNoOut
    if mov.foutpointNo2 is not None:
        sql_3 = sql_3 + " and foutpoint_no2 = :foutpointNo2 "
        param["foutpointNo2"] = mov.foutpointNo2
    if mov.fbillno is not None:
        sql_3 = sql_3 + " and a.fbillno like concat('%', :fbillno ,'%')"
        param["fbillno"] = mov.fbillno
    if mov.fmovuser is not None:
        sql_3 = sql_3 + " and fmovuser = :fmovuser "
        param["fmovuser"] = mov.fmovuser
    if mov.fbillstate is not None:
        sql_3 = sql_3 + " and fbillstate = :fbillstate "
        param["fbillstate"] = mov.fbillstate
    if (mov.ftakedateStart is not None) and (mov.ftakedateEnd is not None):
        sql_3 = sql_3 + " and to_char(fmovdate,'yyyymmdd') between :ftakedateStart and  :ftakedateEnd "
        param["ftakedateStart"] = mov.ftakedateStart
        param["ftakedateEnd"] = mov.ftakedateEnd
    sql_3 = sql_3 + " order by b.fbillno,b.fline"
    data_detail = db.execute(text(sql_3), param).fetchall()

    data_list = []
    for key in range(len(data)):
        d1 = {}
        d1['fbillno'] = data[key][0]
        d1['fsiteno'] = data[key][1]
        d1['fmovuser'] = data[key][2]
        d1['fmovdate'] = data[key][3]
        d1['fpointNoOut'] = data[key][4]
        d1['foutpointNo2'] = data[key][5]
        d1['fitemcount'] = data[key][6]
        d1['fpiececount'] = data[key][7]
        d1['ftotalamt'] = data[key][8]
        d1['fexplanation'] = data[key][9]
        d1['fupddate'] = data[key][10]
        d1['foperator'] = data[key][11]
        d1['fcheckdate'] = data[key][12]
        d1['fcheckuser'] = data[key][13]
        d1['fbillstate'] = data[key][14]
        sbillno = data[key][0]

        detail_list = []
        for j1 in range(len(data_detail)):
            if (str(data_detail[j1][0]) == str(sbillno)):
                d2 = {}
                d2['fbillno'] = data_detail[j1][0]
                d2['fline'] = data_detail[j1][1]
                d2['fpointsizeout'] = data_detail[j1][2]
                d2['fpointsizein'] = data_detail[j1][3]
                d2['fequipmentbarcode'] = data_detail[j1][4]
                d2['feuipmentid'] = data_detail[j1][5]
                d2['fmacno'] = data_detail[j1][6]
                d2['fequipmenttag'] = data_detail[j1][7]
                d2['flinkmac'] = data_detail[j1][8]
                d2['flinkmark'] = data_detail[j1][9]
                d2['fbuyprice'] = data_detail[j1][10]
                detail_list.append(d2)

        d1["details"] = detail_list
        data_list.append(d1)

    info_dict = {"list": data_list, "limit": mov.limit, "page": mov.pageIndex,
                 "pages": (count + mov.limit - 1) // mov.limit,
                 "total": count}
    return BaseResponse.success(data=info_dict)


class TcMovDetailCreate(BaseModel):
    """设备调拨明细创建模型"""
    fline: int = Field(..., description="行号")
    fpointsizeout: str = Field("", description="调出点位规格")
    fpointsizein: str = Field("", description="调入点位规格")
    fequipmentbarcode: str = Field("", description="设备条码")
    feuipmentid: int = Field(..., description="设备ID")
    fmacno: str = Field("", description="MAC地址")
    fequipmenttag: str = Field("", description="设备标签")
    flinkmac: Optional[str] = Field(None, description="关联MAC地址")
    flinkmark: Optional[str] = Field(None, description="关联标记")
    fbuyprice: float = Field(0, description="采购价格")


class TcMovHeaderCreate(BaseModel):
    """设备调拨表头创建模型"""
    fbillno: str = Field(..., description="调拨单号")
    fsiteNo: str = Field(..., description="站点编号")
    fpointNoOut: str = Field(..., description="调出点位编号")
    foutpointNo2: str = Field(..., description="调出二级点位编号")
    fmovuser: str = Field(..., description="调拨人")
    fmovdate: date = Field(..., description="调拨日期")
    foperator: str = Field(..., description="操作员")
    fbillstate: int = Field(1, description="单据状态，默认1")
    fupddate: date = Field(..., description="更新日期")
    fexplanation: str = Field("", description="调拨说明")
    details: List[TcMovDetailCreate] = Field(..., description="调拨明细列表")


@mov_head_router.post("/create", summary="新增设备调拨信息")
def create_mov_head(tc: TcMovHeaderCreate, db: Session = Depends(get_db)):
    try:
        fitemcount = 0
        fpiececount = 0
        ftotalamt = 0
        fbillno = tc.fbillno
        # 返回内容
        d1 = {}
        detail_list = []
        with db.begin():
            jsondata = tc.details
            for data in jsondata:
                sql = "insert into lims.s_tc_mov_detail "
                sql = sql + " values( :fbillno , :fline , :fponitSizeOut , :fponitSizeIn , :fequipmentBarcode , :feuipmentid , :fmacno , :fequipmenttag , :flinkmac , :flinkmark , :fbuyprice)"
                db.execute(text(sql), {
                    "fbillno": fbillno,
                    "fline": data.fline,
                    "fponitSizeOut": data.fpointsizeout,
                    "fponitSizeIn": data.fpointsizein,
                    "fequipmentBarcode": data.fequipmentbarcode,
                    "feuipmentid": data.feuipmentid,
                    "fmacno": data.fmacno,
                    "fequipmenttag": data.fequipmenttag,
                    "flinkmac": data.flinkmac,
                    "flinkmark": data.flinkmark,
                    "fbuyprice": data.fbuyprice
                })
                fitemcount = fitemcount + 1
                fpiececount = fpiececount + 1
                # 返回明细
                d2 = {}
                d2['fbillno'] = fbillno
                d2['fline'] = data.fline
                d2['fequipmentbarcode'] = data.fequipmentbarcode
                d2['fequipmenttag'] = data.fequipmenttag
                d2['feuipmentid'] = data.feuipmentid
                d2['fbuyprice'] = float(data.fbuyprice)
                d2['flinkmac'] = None
                d2['flinkmark'] = None
                d2['fpointSize'] = None
                d2['fmacno'] = data.fmacno
                d2['fpointSizeIn'] = data.fpointsizein
                detail_list.append(d2)
            sql = "insert into lims.s_tc_mov_head(fbillno,fsite_no,fpoint_no_out,foutpoint_no2,fmovuser,fmovdate,fitemcount,fpiececount,fexplanation,fupddate,foperator,fbillstate)"
            sql = sql + " values( :fbillno , :fsiteNo , :fpointNoOut , :foutpointNo2 , :fmovuser , :fmovdate , :fitemcount , :fpiececount , :fexplanation , :fupddate , :foperator , :fbillstate )"
            db.execute(text(sql), {
                "fbillno": fbillno,
                "fsiteNo": tc.fsiteNo,
                "fpointNoOut": tc.fpointNoOut,
                "foutpointNo2": tc.foutpointNo2,
                "fmovuser": tc.fmovuser,
                "fmovdate": tc.fmovdate,
                "fitemcount": fitemcount,
                "fpiececount": fpiececount,
                "fexplanation": tc.fexplanation,
                "fupddate": tc.fupddate,
                "foperator": tc.foperator,
                "fbillstate": tc.fbillstate
            })
            # 返回
            d1['fbillno'] = fbillno
            d1['fsiteno'] = tc.fsiteNo
            d1['fmovdate'] = tc.fmovdate
            d1['fmovuser'] = tc.fmovuser
            d1['foutpointNo2'] = tc.foutpointNo2
            d1['fitemcount'] = fitemcount
            d1['fpiececount'] = fpiececount
            d1['fpointNoOut'] = tc.fpointNoOut
            d1['ftakedateEnd'] = None
            d1['ftakedateStart'] = None
            d1['fsrcbillno'] = ""
            d1['fexplanation'] = tc.fexplanation
            d1['fupddate'] = tc.fupddate
            d1['foperator'] = tc.foperator
            d1['fcheckdate'] = None
            d1['fcheckuser'] = None
            d1['ftotalamt'] = None
            d1['fbillstate'] = 1
            d1["details"] = detail_list
            return BaseResponse.success(data=d1)
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")


@mov_head_router.post("/update", summary="修改设备调拨信息")
def update_mov_head(tc: TcMovHeaderCreate, db: Session = Depends(get_db), token: str = Depends(oauth2_scheme)):
    try:
        user_id = EncryptionMiddleware.get_current_user(token)
        fitemcount = 0
        fpiececount = 0
        ftotalamt = 0
        fbillno = tc.fbillno
        fbillstate = tc.fbillstate
        with db.begin():
            if (fbillstate == 8):
                print("作废")
                sql = "update lims.s_tc_mov_head "
                sql = sql + " set fbillstate=8,fcheckdate=now(),fcheckuser= :userid "
                sql = sql + " where fbillno= :fbillno "
                db.execute(text(sql), {
                    "fbillno": fbillno,
                    "userid": user_id
                })
            else:
                sql = "delete from lims.s_tc_mov_detail where fbillno= :fbillno "
                db.execute(text(sql), {
                    "fbillno": fbillno,
                })
                jsondata = tc.details
                for data in jsondata:
                    sql = "insert into lims.s_tc_mov_detail "
                    sql = sql + " values( :fbillno , :fline , :fponitSizeOut , :fponitSizeIn , :fequipmentBarcode , :feuipmentid , :fmacno , :fequipmenttag , :flinkmac , :flinkmark , :fbuyprice)"
                    db.execute(text(sql), {
                        "fbillno": fbillno,
                        "fline": data.fline,
                        "fponitSizeOut": data.fpointsizeout,
                        "fponitSizeIn": data.fpointsizein,
                        "fequipmentBarcode": data.fequipmentbarcode,
                        "feuipmentid": data.feuipmentid,
                        "fmacno": data.fmacno,
                        "fequipmenttag": data.fequipmenttag,
                        "flinkmac": data.flinkmac,
                        "flinkmark": data.flinkmark,
                        "fbuyprice": data.fbuyprice
                    })
                    fitemcount = fitemcount + 1
                    fpiececount = fpiececount + 1
                sql = "update lims.s_tc_mov_head "
                sql = sql + " set fmovdate= :fmovdate ,fmovuser= :fmovuser ,fexplanation= :fexplanation "
                sql = sql + ",fitemcount= :fitemcount ,fpiececount= :fpiececount "
                sql = sql + ",fupddate=now(),foperator= :userid "
                sql = sql + " where fbillno= :fbillno "
                db.execute(text(sql), {
                    "fmovdate":tc.fmovdate,
                    "fmovuser":tc.fmovuser,
                    "fexplanation":tc.fexplanation,
                    "fitemcount":fitemcount,
                    "fpiececount":fpiececount,
                    "userid":user_id,
                    "fbillno":fbillno,
                })
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")
